CREATE OR REPLACE Package AP_WEB_ACCTG_PKG Authid Current_User As
  /* $Header: apwaccts.pls 120.8.12010000.4 2009/02/11 08:02:51 rveliche ship $ */

  /*=======================================================================+
  |  Declare PUBLIC Data Types and Variables
  +=======================================================================*/

  /*=======================================================================+
  |  BuildAccount values for p_build_mode
  |
  |  C_DEFAULT
  |      - for defaulting new distribution segments in Expenses Entry Allocations page
  |      - for rebuilding distribution segments in Expenses Entry Allocations page
  |        when expense type or expense report header cost center has changed
  |  C_CUSTOM_BUILD_ONLY
  |      - for rebuilding distribution segments in Expenses Entry Allocations page
  |        when user presses Update/Next (Online Validation disabled)
  |  C_DEFAULT_VALIDATE
  |      - for defaulting/validating employee segments in Workflow AP Server Side Validation
  |      - for defaulting/validating distribution segments in Workflow AP Server Side Validation
  |        (LLA is disabled)
  |  C_BUILD_VALIDATE
  |      - for building/validating distribution segments in Audit
  |        when expense type has changed
  |  C_VALIDATE
  |      - for validating distribution segments (with a potential for rebuild):
  |        a. when user presses Update/Next in Expenses Entry Allocations page
  |           (Online Validation enabled)
  |        b. Workflow AP Server Side Validation (LLA is enabled without Online Validation)
  |
  +=======================================================================*/
  C_DEFAULT           Constant Varchar2(30) := 'DEFAULT';
  C_CUSTOM_BUILD_ONLY Constant Varchar2(30) := 'CUSTOM_BUILD_ONLY';
  C_DEFAULT_VALIDATE  Constant Varchar2(30) := 'DEFAULT_VALIDATE';
  C_BUILD_VALIDATE    Constant Varchar2(30) := 'BUILD_VALIDATE';
  C_VALIDATE          Constant Varchar2(30) := 'VALIDATE';

  Type t_cost_center_segnum_table Is Table Of Number Index By Binary_Integer;

  Procedure GetEmployeeCostCenter(p_employee_id In Number
                                 ,p_emp_ccid    In Number
                                 ,p_cost_center Out Nocopy Varchar2);

  Function GetCostCenter(p_ccid                 In Number
                        ,p_chart_of_accounts_id In Number Default Null) Return Varchar2;

  Procedure GetCostCenterApprovalRule(p_alphanumeric_allowed_flag Out Nocopy Varchar2
                                     ,p_uppercase_only_flag       Out Nocopy Varchar2
                                     ,p_numeric_mode_enabled_flag Out Nocopy Varchar2
                                     ,p_maximum_size              Out Nocopy Number);

  Procedure ValidateCostCenter(p_cost_center          In Varchar2
                              ,p_employee_id          In Number
                              ,p_emp_set_of_books_id  In Number
                              ,p_default_emp_ccid     In Varchar2
                              ,p_chart_of_accounts_id In Number
                              ,p_cost_center_valid    Out Nocopy Boolean);

  Procedure GetExpenseTypeCostCenter(p_exp_type_parameter_id In Number
                                    ,p_cost_center           Out Nocopy Varchar2);

  Procedure GetCostCenterSegmentName(p_cost_center_segment_name Out Nocopy Varchar2);

  Procedure GetDistributionSegments(p_chart_of_accounts_id   In GL_SETS_OF_BOOKS.chart_of_accounts_id%Type
                                   ,p_report_distribution_id In Number
                                   ,p_segments               Out Nocopy AP_OIE_KFF_SEGMENTS_T);

  Procedure GetConcatenatedSegments(p_chart_of_accounts_id  In Number
                                   ,p_segments              In AP_OIE_KFF_SEGMENTS_T
                                   ,p_concatenated_segments Out Nocopy Varchar2);

  Procedure BuildAccount(p_report_header_id      In Number
                        ,p_report_line_id        In Number
                        ,p_employee_id           In Number
                        ,p_cost_center           In Varchar2
                        ,p_line_cost_center      In Varchar2
                        ,p_exp_type_parameter_id In Number
                        ,p_segments              In AP_OIE_KFF_SEGMENTS_T
                        ,p_ccid                  In Number
                        ,p_build_mode            In Varchar2
                        ,p_new_segments          Out Nocopy AP_OIE_KFF_SEGMENTS_T
                        ,p_new_ccid              Out Nocopy Number
                        ,p_return_error_message  Out Nocopy Varchar2);

  Procedure BuildDistProjectAccount(p_report_header_id       In Number
                                   ,p_report_line_id         In Number
                                   ,p_report_distribution_id In Number
                                   ,p_exp_type_parameter_id  In Number
                                   ,p_new_segments           Out Nocopy AP_OIE_KFF_SEGMENTS_T
                                   ,p_new_ccid               Out Nocopy Number
                                   ,p_return_error_message   Out Nocopy Varchar2
                                   ,p_return_status          Out Nocopy Varchar2);

  -- Bug: 6936055, GMS Integration, Award Validation
  Procedure ValidateProjectAccounting(p_report_line_id       In Number
                                     ,p_web_parameter_id     In Number
                                     ,p_project_id           In Number
                                     ,p_task_id              In Number
                                     ,p_award_id             In Number
                                     ,p_award_number         In Varchar2
                                     ,p_expenditure_org_id   In Number
                                     ,p_amount               In Number
                                     ,p_return_error_message Out Nocopy Varchar2
                                     ,p_msg_count            Out Nocopy Number
                                     ,p_msg_data             Out Nocopy Varchar2);

  -- Bug: 6631437, CC Segment not rendered if it has a parent
  Procedure GetDependentSegmentValue(p_employee_id In Number
                                    ,p_vset_name   In Varchar2
                                    ,p_seg_value   Out Nocopy Varchar2);

End AP_WEB_ACCTG_PKG;
/
CREATE OR REPLACE Package Body AP_WEB_ACCTG_PKG As
  /* $Header: apwacctb.pls 120.14.12010000.7 2010/03/14 16:42:08 rveliche ship $ */

  --Cache for FND_FLEX_APIS.GET_QUALIFIER_SEGNUM
  gqs_cost_center_segnum t_cost_center_segnum_table;

  Procedure GetEmployeeCostCenter(p_employee_id In Number
                                 ,p_emp_ccid    In Number
                                 ,p_cost_center Out Nocopy Varchar2) Is
  
    l_debug_info Varchar2(200);
    l_debug_info_base Varchar2(4000);
  Begin
  
    --
    -- Call CustomDefaultCostCenter for cost center, otherwise retrieve
    -- cost center segment by deriving from employee ccid.
    --
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' AP_WEB_CUST_DFLEX_PKG.CustomDefaultCostCenter';
    -----------------------------------------------------
    p_cost_center := AP_WEB_CUST_DFLEX_PKG.CustomDefaultCostCenter(p_employee_id);
  
    If (p_cost_center Is Null)
    Then
      p_cost_center := GetCostCenter(p_emp_ccid);
    End If;
  End GetEmployeeCostCenter;

  Function GetCostCenter(p_ccid                 In Number
                        ,p_chart_of_accounts_id In Number) Return Varchar2 Is
  
    l_debug_info Varchar2(200);
    l_debug_info_base Varchar2(4000);
  
    l_chart_of_accounts_id Number := p_chart_of_accounts_id;
    l_segments             FND_FLEX_EXT.SEGMENTARRAY;
    l_num_of_segments      Number;
    l_cost_ctr_seg_num     Number;
  
  Begin
  
    -- Only get the chart of accounts ID if none/null is passed in
    If (l_chart_of_accounts_id Is Null)
    Then
      -----------------------------------------------------
      l_debug_info := l_debug_info_base || ' AP_WEB_DB_AP_INT_PKG.GetCOAofSOB';
      -----------------------------------------------------
      If (Not AP_WEB_DB_AP_INT_PKG.GetCOAofSOB(l_chart_of_accounts_id))
      Then
        l_chart_of_accounts_id := Null;
      End If;
    End If;
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Retrieve segments from code combination id';
    -----------------------------------------------------
    If (Not FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#', l_chart_of_accounts_id, p_ccid, l_num_of_segments, l_segments))
    Then
      Return Null;
    End If;
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  Cost Center segment number';
    -----------------------------------------------------
    Begin
      -- Check cache
      l_cost_ctr_seg_num := gqs_cost_center_segnum(l_chart_of_accounts_id);
    Exception
      -- Not found in cache, retrieve segment number
      When NO_DATA_FOUND Then
        If (Not FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(101, 'GL#', l_chart_of_accounts_id, 'FA_COST_CTR', l_cost_ctr_seg_num))
        Then
          Return Null;
        End If;
      
        -- Put segment number in cache for chart of accounts ID
        gqs_cost_center_segnum(l_chart_of_accounts_id) := l_cost_ctr_seg_num;
    End;
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  Cost Center segment';
    -----------------------------------------------------
    Return l_segments(l_cost_ctr_seg_num);
  
  Exception
    When Others Then
      AP_WEB_DB_UTIL_PKG.RaiseException('GetCostCenter');
      APP_EXCEPTION.RAISE_EXCEPTION;
    
  End GetCostCenter;

  Procedure GetCostCenterApprovalRule(p_alphanumeric_allowed_flag Out Nocopy Varchar2
                                     ,p_uppercase_only_flag       Out Nocopy Varchar2
                                     ,p_numeric_mode_enabled_flag Out Nocopy Varchar2
                                     ,p_maximum_size              Out Nocopy Number) Is
  
    l_debug_info Varchar2(200);
    l_debug_info_base Varchar2(4000);
  
    l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%Type;
    l_cost_center_seg_num  Number;
    l_appcol_name          fnd_id_flex_segments_vl.application_column_name%Type;
    l_seg_name             fnd_id_flex_segments_vl.segment_name%Type;
    l_prompt               fnd_id_flex_segments_vl.form_left_prompt%Type;
    l_value_set_name       fnd_flex_value_sets.flex_value_set_name%Type;
  
  Begin
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' AP_WEB_DB_AP_INT_PKG.GetCOAofSOB';
    -----------------------------------------------------
    If (Not AP_WEB_DB_AP_INT_PKG.GetCOAofSOB(l_chart_of_accounts_id))
    Then
      l_chart_of_accounts_id := Null;
    End If;
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  the segment number corresponding to the costcenter qualifier.';
    -----------------------------------------------------
    If (Not FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(101, 'GL#', l_chart_of_accounts_id, 'FA_COST_CTR', l_cost_center_seg_num))
    Then
      Return;
    End If;
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  the valueset associated with the Department (costcenter qualifier)';
    -----------------------------------------------------
    If (Not FND_FLEX_APIS.GET_SEGMENT_INFO(101, 'GL#', l_chart_of_accounts_id, l_cost_center_seg_num, l_appcol_name, l_seg_name, l_prompt, l_value_set_name))
    Then
      Return;
    End If;
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  Cost Center Approval rules';
    -----------------------------------------------------
    Select alphanumeric_allowed_flag
          ,uppercase_only_flag
          ,numeric_mode_enabled_flag
          ,maximum_size
      Into p_alphanumeric_allowed_flag
          ,p_uppercase_only_flag
          ,p_numeric_mode_enabled_flag
          ,p_maximum_size
      From fnd_flex_value_sets
     Where flex_value_set_name Like l_value_set_name;
  
  Exception
    When Others Then
      AP_WEB_DB_UTIL_PKG.RaiseException('GetCostCenterApprovalRule');
      APP_EXCEPTION.RAISE_EXCEPTION;
    
  End GetCostCenterApprovalRule;

  /*Bug 2690715 : Declared a function to get the dependent segment
                  of cost center segment.
  */
  Function GetDependentSegment(p_value_set_name       In fnd_flex_value_sets.flex_value_set_name%Type
                              ,p_chart_of_accounts_id In AP_WEB_DB_AP_INT_PKG.glsob_chartOfAccountsID
                              ,p_dependent_seg_num    Out Nocopy Number) Return Boolean Is
  
    l_parent_flex_value_set_id fnd_flex_value_sets.parent_flex_value_set_id%Type;
  
  Begin
  
    Select PARENT_FLEX_VALUE_SET_ID
      Into l_parent_flex_value_set_id
      From fnd_flex_value_sets
     Where flex_value_set_name Like p_value_set_name
           And PARENT_FLEX_VALUE_SET_ID Is Not Null;
  
    If (l_parent_flex_value_set_id Is Not Null)
    Then
    
      Select s.segment_num
        Into p_dependent_seg_num
        From fnd_id_flex_segments         s
            ,fnd_segment_attribute_values sav
            ,fnd_segment_attribute_types  sat
       Where s.application_id = 101
             And s.id_flex_code = 'GL#'
             And s.id_flex_num = p_chart_of_accounts_id
             And s.enabled_flag = 'Y'
             And s.application_column_name = sav.application_column_name
             And sav.application_id = 101
             And sav.id_flex_code = 'GL#'
             And sav.id_flex_num = p_chart_of_accounts_id
             And sav.attribute_value = 'Y'
             And sav.segment_attribute_type = sat.segment_attribute_type
             And sat.application_id = 101
             And sat.id_flex_code = 'GL#'
             And sat.unique_flag = 'Y'
             And s.FLEX_VALUE_SET_ID = l_parent_flex_value_set_id
             And rownum = 1;
    End If;
  
    Return True;
  
  Exception
    When NO_DATA_FOUND Then
      Return False;
    When Others Then
      AP_WEB_DB_UTIL_PKG.RaiseException('GetDependentSegment');
      APP_EXCEPTION.RAISE_EXCEPTION;
      Return False;
  End GetDependentSegment;

  Procedure ValidateCostCenter(p_cost_center          In Varchar2
                              ,p_employee_id          In Number
                              ,p_emp_set_of_books_id  In Number
                              ,p_default_emp_ccid     In Varchar2
                              ,p_chart_of_accounts_id In Number
                              ,p_cost_center_valid    Out Nocopy Boolean) Is
  
    l_debug_info Varchar2(200);
    l_debug_info_base Varchar2(4000);
  
    l_flex_segment_delimiter Varchar2(1);
    l_cost_center_seg_num    Number;
    l_num_segments           Number;
    l_default_emp_segments   FND_FLEX_EXT.SEGMENTARRAY;
    l_appcol_name            fnd_id_flex_segments_vl.application_column_name%Type;
    l_seg_name               fnd_id_flex_segments_vl.segment_name%Type;
    l_prompt                 fnd_id_flex_segments_vl.form_left_prompt%Type;
    l_value_set_name         fnd_flex_value_sets.flex_value_set_name%Type;
    l_dependent_seg_num      Number := 0;
    l_concatenated_segments  Varchar2(2000);
  
  Begin
  
    -- Get the character used as the segment delimiter. This would be
    -- used to prepare the concatenated segment from segment array to a string.
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  segment delimiter';
    -----------------------------------------------------
    l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER('SQLGL', 'GL#', p_chart_of_accounts_id);
  
    -- Get the segment number corresponding to the costcenter qualifier.
    -- This is used to overlay the costcenter segment
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  cost center segment number';
    -----------------------------------------------------
    If (Not FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(101, 'GL#', p_chart_of_accounts_id, 'FA_COST_CTR', l_cost_center_seg_num))
    Then
      p_cost_center_valid := False;
      Return;
    End If;
  
    -- Get the segment array and number of segments for the employee
    -- code combination id.
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  employee default segments';
    -----------------------------------------------------
    If (Not FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#', p_chart_of_accounts_id, p_default_emp_ccid, l_num_segments, l_default_emp_segments))
    Then
      p_cost_center_valid := False;
      Return;
    End If;
  
    /*Bug 2690715 : Called function to get the dependent segment
                of cost center segment and then do the validation.
    */
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' FND_FLEX_APIS.GET_SEGMENT_INFO';
    -----------------------------------------------------
    If (FND_FLEX_APIS.GET_SEGMENT_INFO(101, 'GL#', p_chart_of_accounts_id, l_cost_center_seg_num, l_appcol_name, l_seg_name, l_prompt, l_value_set_name))
    Then
    
      -----------------------------------------------------
      l_debug_info := l_debug_info_base || ' Get DependentSegment';
      -----------------------------------------------------
      If (GetDependentSegment(l_value_set_name, p_chart_of_accounts_id, l_dependent_seg_num))
      Then
        Null;
      End If;
    
    End If;
  
    /*Bug 2690715 : Dont set the segment to NULL , if dependent valueset
                    of cost center segment exists.
    */
    If (l_dependent_seg_num Is Null)
    Then
      For i In 1 .. l_num_segments
      Loop
        l_default_emp_segments(i) := '';
      End Loop;
    End If;
  
    -- Overlay the costcenter segment with the costcenter entered in the
    -- expense report
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Overlay cost center segment with expense report cost center';
    -----------------------------------------------------
    l_default_emp_segments(l_cost_center_seg_num) := p_cost_center;
  
    -- Get the concatenated segments with all segments set to null, except the
    -- costcenter, Concatenates segments from segment array
    -- (l_default_emp_segments) to a string(l_concatenated_segments).
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Concatenate segments';
    -----------------------------------------------------
    l_concatenated_segments := FND_FLEX_EXT.concatenate_segments(l_num_segments, l_default_emp_segments, l_flex_segment_delimiter);
  
    /* -----------------------------------------------------------------------+
    + Validate only the costcenter segment, since we are passing other        +
    + segments as null we need to set allow_nulls to true                     +
    + Allow_nulls will allow required segments that are NULL to be valid      +
    + allow_orphans will validate dependent segment values without associated +
    + parent values, allowing ANY possible dependent value, regardless        +
    + of what the parent value would be,  to be considered valid.             +
    +------------------------------------------------------------------------*/
    /*Bug 2706584: Passed VRULE as a parameter to Validate_Segs so that
                   it does not pass the cost center for which
                   Posting Allowed is  Unchecked.
    */
  
    /*Bug 2690715:Validate using only segment of COST CENTER if
                  No Dependent Segment is present.Else validate
                  using Combination,Flex Field does not support
                  partial combination validation. i.e Like 01-520----
                 can not be successfully validated.
    */
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Validate segments';
    -----------------------------------------------------
    If (l_dependent_seg_num Is Null)
    Then
      If (fnd_flex_keyval.validate_segs(operation        => 'CHECK_SEGMENTS'
                                       ,appl_short_name  => 'SQLGL'
                                       ,key_flex_code    => 'GL#'
                                       ,structure_number => p_chart_of_accounts_id
                                       ,concat_segments  => l_concatenated_segments
                                       ,VRULE            => 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED\nI\nNAME=AP_ALL_POSTING_NA\nY\0\nSUMMARY_FLAG\nI\nNAME=Flex-Parent not allowed\nN'
                                       ,allow_nulls      => True
                                       ,allow_orphans    => True))
      Then
        p_cost_center_valid := True;
        Return;
      Else
        p_cost_center_valid := False;
        Return;
      End If;
    Else
      If (fnd_flex_keyval.validate_segs(operation        => 'CHECK_COMBINATION'
                                       ,appl_short_name  => 'SQLGL'
                                       ,key_flex_code    => 'GL#'
                                       ,structure_number => p_chart_of_accounts_id
                                       ,concat_segments  => l_concatenated_segments
                                       ,VRULE            => 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED\nI\nNAME=AP_ALL_POSTING_NA\nY\0\nSUMMARY_FLAG\nI\nNAME=Flex-Parent not allowed\nN'
                                       ,allow_nulls      => True
                                       ,allow_orphans    => True))
      Then
        p_cost_center_valid := True;
        Return;
      Else
        p_cost_center_valid := False;
        Return;
      End If;
    End If;
  
  Exception
    When NO_DATA_FOUND Then
      p_cost_center_valid := False;
    
    When Others Then
      p_cost_center_valid := False;
    
  End ValidateCostCenter;

  Procedure GetExpenseTypeCostCenter(p_exp_type_parameter_id In Number
                                    ,p_cost_center           Out Nocopy Varchar2) Is
  
    l_debug_info Varchar2(200);
    l_debug_info_base Varchar2(4000);
  
    l_chart_of_accounts_id    Number;
    l_flex_segment_delimiter  Varchar2(1);
    l_cost_center_seg_num     Number;
    l_num_segments            Number;
    l_exp_type_template_array FND_FLEX_EXT.SEGMENTARRAY;
    l_FlexConcactenated       AP_EXPENSE_REPORT_PARAMS.flex_concactenated%Type;
  
  Begin
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' AP_WEB_DB_AP_INT_PKG.GetCOAofSOB';
    -----------------------------------------------------
    If (Not AP_WEB_DB_AP_INT_PKG.GetCOAofSOB(l_chart_of_accounts_id))
    Then
      l_chart_of_accounts_id := Null;
    End If;
  
    -- Get the character used as the segment delimiter. This would be
    -- used to prepare the concatenated segment from segment array to a string.
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  segment delimeter';
    -----------------------------------------------------
    l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER('SQLGL', 'GL#', l_chart_of_accounts_id);
  
    -- Get the segment number corresponding to the costcenter qualifier.
    -- This is used to get the cost center for the expense type
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  segment number';
    -----------------------------------------------------
    If (Not FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(101, 'GL#', l_chart_of_accounts_id, 'FA_COST_CTR', l_cost_center_seg_num))
    Then
      Return;
    End If;
  
    Begin
      -----------------------------------------------------
      l_debug_info := l_debug_info_base || ' Get  FLEX_CONCACTENATED';
      -----------------------------------------------------
      Select FLEX_CONCACTENATED Into l_FlexConcactenated From ap_expense_report_params_all Where parameter_id = p_exp_type_parameter_id;
    Exception
      When Others Then
        Return;
    End;
  
    /* Bug 4212250: Since l_FlexConcactenated is null, no need to
    *              proceed further.
    */
    If l_FlexConcactenated Is Null
    Then
      Return;
    End If;
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Break up segments';
    -----------------------------------------------------
    l_num_segments := FND_FLEX_EXT.Breakup_Segments(l_FlexConcactenated, l_flex_segment_delimiter, l_exp_type_template_array);
  
    If nvl(l_num_segments, 0) = 0
    Then
      Return;
    Else
      If (l_exp_type_template_array(l_cost_center_seg_num) Is Not Null)
      Then
        p_cost_center := l_exp_type_template_array(l_cost_center_seg_num);
      Else
        Return;
      End If;
    End If;
  
  Exception
    When Others Then
      AP_WEB_DB_UTIL_PKG.RaiseException('GetExpenseTypeCostCenter');
      APP_EXCEPTION.RAISE_EXCEPTION;
    
  End GetExpenseTypeCostCenter;

  Procedure GetCostCenterSegmentName(p_cost_center_segment_name Out Nocopy Varchar2) Is
  
    l_debug_info Varchar2(200);
  
    l_chart_of_accounts_id Number;
  
  Begin
  
    /* This query will exclude terminated contingent
    workers and terminated employees who became
    contingent workers to avoid duplicate rows.
    Otherwise 2 rows for the same person could be
    possible. */
    -- Query up the Chart of Accounts Id for the Employee
    Select GS.chart_of_accounts_id
      Into l_chart_of_accounts_id
      From ap_system_parameters S
          ,gl_sets_of_books     GS
     Where GS.set_of_books_id = S.set_of_books_id
           And rownum = 1;
    -- Get the Column Name which implements the Cost Center Segment
    If (FND_FLEX_APIS.GET_SEGMENT_COLUMN(101, 'GL#', l_chart_of_accounts_id, 'FA_COST_CTR', p_cost_center_segment_name))
    Then
      Return;
    Else
      -- RAISE EXCEPTION THAT COST CTR IMPLEMENTATION COULD NOT BE FOUND!!!
      p_cost_center_segment_name := 'SEGMENT2';
      Return;
    End If;
  
  Exception
    When NO_DATA_FOUND Then
      -- RAISE EXCEPTION FOR INVALID CHART OF ACCOUNTS
      p_cost_center_segment_name := 'SEGMENT2';
      Return;
    When Others Then
      -- RAISE SOME EXCEPTION
      p_cost_center_segment_name := 'SEGMENT2';
      Return;
    
  End GetCostCenterSegmentName;

  -------------------------------------------------------------------------
  --
  -- PROCEDURE GetDistributionSegments
  --
  -- Parameters
  --        p_chart_of_accounts_id          Flexfield structure num
  --        p_report_distribution_id        report_distribution_id
  --        p_segments                      AP_OIE_KFF_SEGMENTS_T
  --
  -- Returns VARCHAR2
  --   Returns the segment values array of the key flexfield
  --
  -- Description
  --   There is no equivalent for this function. This function takes
  --   the segment values stored in ap_exp_report_dists and returns
  --   the segment values array according to the kff structure.
  --
  -- Modification History
  --  ALING    30-SEP-04        Created.
  --
  -------------------------------------------------------------------------
  Procedure GetDistributionSegments(p_chart_of_accounts_id   In GL_SETS_OF_BOOKS.chart_of_accounts_id%Type
                                   ,p_report_distribution_id In Number
                                   ,p_segments               Out Nocopy AP_OIE_KFF_SEGMENTS_T) Is
    --------------------------------------------------------------------------------
  
    l_debug_info             Varchar2(1000);
    l_debug_info_base Varchar2(4000);
    
    sqlstmt                  Varchar2(2000);
    colname                  fnd_id_flex_segments.application_column_name%Type;
    c                        Integer;
    l_concat_segments        Varchar2(1000);
    rows                     Integer;
    l_num_segments           Number;
    l_segment_array          FND_FLEX_EXT.SEGMENTARRAY;
    l_flex_segment_delimiter Varchar2(1);
  
    Cursor cflex(p_chart_accounts_id In Number) Is
      Select flex.application_column_name
        From fnd_id_flex_segments flex
       Where flex.application_id = 101
             And flex.id_flex_code = 'GL#'
             And flex.id_flex_num = p_chart_accounts_id
             And flex.enabled_flag = 'Y'
             And flex.display_flag = 'Y'
       Order By segment_num;
  
  Begin
  
    -- Get the character used as the segment delimiter. This would be
    -- used to prepare the concatenated segment from segment array to a string.
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  segment delimiter';
    -----------------------------------------------------
    l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER('SQLGL', 'GL#', p_chart_of_accounts_id);
  
    ------------------------------------------------------------
    l_debug_info := l_debug_info_base || ' First build the select statment';
    ------------------------------------------------------------
    sqlstmt := Null;
    If cflex%Isopen
    Then
      Close cflex;
    End If;
  
    Open cflex(p_chart_of_accounts_id);
  
    Loop
      Fetch cflex
        Into colname;
      Exit When cflex%Notfound;
    
      If sqlstmt Is Not Null
      Then
        sqlstmt := sqlstmt || '||''' || l_flex_segment_delimiter || '''||' || colname;
      Else
        sqlstmt := colname;
      End If;
    
    End Loop;
  
    sqlstmt := 'SELECT ' || sqlstmt || ' FROM AP_EXP_REPORT_DISTS
              WHERE report_distribution_id = :report_distribution_id';
  
    Close cflex;
  
    -----------------
    -- Now execute it
    -----------------
    c := dbms_sql.open_cursor;
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' parse cursor';
    -----------------------------------------------------
    dbms_sql.parse(c, sqlstmt, dbms_sql.native);
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' bind values to the placeholder';
    -----------------------------------------------------
    dbms_sql.bind_variable(c, ':report_distribution_id', p_report_distribution_id);
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' setup output';
    -----------------------------------------------------
    dbms_sql.define_column(c, 1, l_concat_segments, 1000);
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' execute cursor';
    -----------------------------------------------------
    rows := dbms_sql.execute(c);
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' fetch a row';
    -----------------------------------------------------
    If dbms_sql.fetch_rows(c) > 0
    Then
      -- fetch columns from the row
      dbms_sql.column_value(c, 1, l_concat_segments);
    End If;
  
    dbms_sql.column_value(c, 1, l_concat_segments);
  
    dbms_sql.close_cursor(c);
  
    If cflex%Isopen
    Then
      Close cflex;
    End If;
  
    --------------------------------------------------------------
    l_debug_info := l_debug_info_base || ' Break Up Segments';
    --------------------------------------------------------------
    l_num_segments := FND_FLEX_EXT.Breakup_Segments(l_concat_segments, l_flex_segment_delimiter, l_segment_array);
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Assign values to p_segments';
    -----------------------------------------------------
    p_segments := AP_OIE_KFF_SEGMENTS_T('');
    p_segments.extend(l_segment_array.count);
    For i In 1 .. l_segment_array.count
    Loop
      p_segments(i) := l_segment_array(i);
    End Loop;
  
  Exception
    When Others Then
      If cflex%Isopen
      Then
        Close cflex;
      End If;
      If dbms_sql.is_open(c)
      Then
        dbms_sql.close_cursor(c);
      End If;
    
      If (Sqlcode <> -20001)
      Then
        FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
        FND_MESSAGE.SET_TOKEN('ERROR', Sqlerrm);
        FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'GetDistributionSegments');
        FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
      End If;
      APP_EXCEPTION.RAISE_EXCEPTION;
  End GetDistributionSegments;

  Procedure GetConcatenatedSegments(p_chart_of_accounts_id  In Number
                                   ,p_segments              In AP_OIE_KFF_SEGMENTS_T
                                   ,p_concatenated_segments Out Nocopy Varchar2) Is
  
    l_debug_info Varchar2(100);
    l_debug_info_base Varchar2(4000);
  
    l_flex_segment_delimiter Varchar2(1);
    l_segments               FND_FLEX_EXT.SEGMENTARRAY;
  
  Begin
  
    If (p_chart_of_accounts_id Is Null)
    Then
      FND_MESSAGE.Set_Name('SQLAP', 'OIE_MISS_CHART_OF_ACC_ID');
      Raise AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
    End If;
  
    ----------------------------------------
    l_debug_info := l_debug_info_base || ' Get  Segment Delimiter like .';
    ----------------------------------------
    l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER('SQLGL', 'GL#', p_chart_of_accounts_id);
  
    If (l_flex_segment_delimiter Is Null)
    Then
      Raise AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
    End If; /* l_flex_segment_delimiter IS NULL */
  
    ------------------------------------------------------------------------
    l_debug_info := l_debug_info_base || ' Convert AP_OIE_KFF_SEGMENTS_T to FND_FLEX_EXT.SEGMENTARRAY';
    ------------------------------------------------------------------------
    For i In 1 .. p_segments.count
    Loop
      l_segments(i) := p_segments(i);
    End Loop;
  
    ------------------------------------------------------------------------
    l_debug_info := l_debug_info_base || ' calling FND_FLEX_EXT.concatenate_segments';
    ------------------------------------------------------------------------
    p_concatenated_segments := FND_FLEX_EXT.concatenate_segments(l_segments.count, l_segments, l_flex_segment_delimiter);
  
  Exception
    When Others Then
      AP_WEB_DB_UTIL_PKG.RaiseException('GetConcatenatedSegments');
      APP_EXCEPTION.RAISE_EXCEPTION;
    
  End GetConcatenatedSegments;

  Procedure BuildAccount(p_report_header_id      In Number
                        ,p_report_line_id        In Number
                        ,p_employee_id           In Number
                        ,p_cost_center           In Varchar2
                        ,p_line_cost_center      In Varchar2
                        ,p_exp_type_parameter_id In Number
                        ,p_segments              In AP_OIE_KFF_SEGMENTS_T
                        ,p_ccid                  In Number
                        ,p_build_mode            In Varchar2
                        ,p_new_segments          Out Nocopy AP_OIE_KFF_SEGMENTS_T
                        ,p_new_ccid              Out Nocopy Number
                        ,p_return_error_message  Out Nocopy Varchar2) Is
  
    l_debug_info Varchar2(4000);
    l_debug_info_base Varchar2(4000);
  
    l_default_emp_ccid         AP_WEB_DB_EXPRPT_PKG.expHdr_employeeCCID;
    l_chart_of_accounts_id     Number;
    l_cost_center_seg_num      Number;
    l_flex_segment_delimiter   Varchar2(1);
    l_default_emp_segments     FND_FLEX_EXT.SEGMENTARRAY;
    l_FlexConcactenated        AP_EXPENSE_REPORT_PARAMS.FLEX_CONCACTENATED%Type;
    l_exp_type_template_array  FND_FLEX_EXT.SEGMENTARRAY;
    l_exp_type_sza_array       FND_FLEX_EXT.SEGMENTARRAY;
    l_vendor_sza_array         FND_FLEX_EXT.SEGMENTARRAY;
    l_exp_line_acct_segs_array FND_FLEX_EXT.SEGMENTARRAY;
    l_num_segments             Number := Null;
    l_concatenated_segments    Varchar2(2000);
  
    l_header_attribute4  Varchar2(100); -- special expense item  
    l_header_attribute13 Varchar2(2000);
    lv_is_prepay         varchar2(100);
  
    l_b_exp_line_acct_segs_array   FND_FLEX_EXT.SEGMENTARRAY;
    l_b_num_segments               Number := Null;
    l_b_concatenated_segments      Varchar2(2000);
    l_b_FlexConcactenated          AP_EXPENSE_REPORT_PARAMS.FLEX_CONCACTENATED%Type;
    lv_test varchar2(4000);
  Begin
    --------------------------------------------------------------
    l_debug_info := l_debug_info_base || ' SZA BuildAccount parameters: ';
    l_debug_info := l_debug_info || '   p_report_header_id      = ' || p_report_header_id      ;
    l_debug_info := l_debug_info || '  ,p_report_line_id        = ' || p_report_line_id        ;
    l_debug_info := l_debug_info || '  ,p_employee_id           = ' || p_employee_id           ;
    l_debug_info := l_debug_info || '  ,p_cost_center           = ' || p_cost_center           ;
    l_debug_info := l_debug_info || '  ,p_line_cost_center      = ' || p_line_cost_center      ;
    l_debug_info := l_debug_info || '  ,p_exp_type_parameter_id = ' || p_exp_type_parameter_id ;
    --l_debug_info := l_debug_info || '  ,p_segments              = ' || p_segments              ;
    l_debug_info := l_debug_info || '  ,p_ccid                  = ' || p_ccid                  ;
    l_debug_info := l_debug_info || '  ,p_build_mode            = ' || p_build_mode            ;
    --l_debug_info := l_debug_info || '  ,p_new_segments          = ' || p_new_segments          ;
    l_debug_info := l_debug_info || '  ,p_new_ccid              = ' || p_new_ccid              ;
    l_debug_info := l_debug_info || '  ,p_return_error_message  = ' || p_return_error_message  ;
    --------------------------------------------------------------    
    --Insert Into cmlin_log (message) Values (l_debug_info);
    
    l_debug_info_base := 'SZA p_report_header_id = ' || p_report_header_id;
    l_debug_info_base := l_debug_info_base || ' p_report_line_id = ' || p_report_line_id;
          
  
    If (AP_WEB_CUS_ACCTG_PKG.BuildAccount(p_report_header_id      => p_report_header_id
                                         ,p_report_line_id        => p_report_line_id
                                         ,p_employee_id           => p_employee_id
                                         ,p_cost_center           => p_cost_center
                                         ,p_exp_type_parameter_id => p_exp_type_parameter_id
                                         ,p_segments              => p_segments
                                         ,p_ccid                  => p_ccid
                                         ,p_build_mode            => p_build_mode
                                         ,p_new_segments          => p_new_segments
                                         ,p_new_ccid              => p_new_ccid
                                         ,p_return_error_message  => p_return_error_message))
    Then
    
      --------------------------------------------------------------------
      l_debug_info := l_debug_info_base || ' Custom BuildAccount';
      --------------------------------------------------------------------
    
      Return;
    
    End If;
  
    If (p_build_mode Not In (C_DEFAULT, C_DEFAULT_VALIDATE, C_BUILD_VALIDATE, C_VALIDATE))
    Then
    
      --------------------------------------------------------------------
      l_debug_info := l_debug_info_base || ' Invalid p_build_mode';
      --------------------------------------------------------------------
    
      Return;
    
    End If;
  
    -- Bug: 9467530, Set AFF Validation to null to avoid Value not found in value set error.
    GL_GLOBAL.set_aff_validation('XX', Null);
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get the HR defaulted Employee CCID';
    -----------------------------------------------------
    If (Not AP_WEB_DB_EXPRPT_PKG.GetDefaultEmpCCID(p_employee_id => p_employee_id, p_default_emp_ccid => l_default_emp_ccid))
    Then
      Null;
    End If;
  
    If (l_default_emp_ccid Is Null)
    Then
      FND_MESSAGE.Set_Name('SQLAP', 'AP_WEB_EXP_MISSING_EMP_CCID');
      Raise AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
    End If;
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get the Employee Chart of Accounts ID';
    -----------------------------------------------------
    If (Not AP_WEB_DB_EXPRPT_PKG.GetChartOfAccountsID(p_employee_id => p_employee_id, p_chart_of_accounts_id => l_chart_of_accounts_id))
    Then
      Null;
    End If;
  
    If (l_chart_of_accounts_id Is Null)
    Then
      FND_MESSAGE.Set_Name('SQLAP', 'OIE_MISS_CHART_OF_ACC_ID');
      Raise AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
    End If;
  
    -----------------------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get employee default ccid account segments';
    -----------------------------------------------------------------
    If (l_default_emp_ccid Is Not Null)
    Then
      If (Not FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#', l_chart_of_accounts_id, l_default_emp_ccid, l_num_segments, l_default_emp_segments))
      Then
        Raise AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
      End If; /* GET_SEGMENTS */
    End If;
  
    ----------------------------------------
    l_debug_info := l_debug_info_base || ' Get  Cost Center Segment Number';
    ----------------------------------------
    If (Not FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(101, 'GL#', l_chart_of_accounts_id, 'FA_COST_CTR', l_cost_center_seg_num))
    Then
      /* We could not find the cost center segment, but we can still overlay the
      * expense type mask, so do nothing */
      Null;
    End If;
  
    ----------------------------------------
    l_debug_info := l_debug_info_base || ' Get  Segment Delimiter like .';
    ----------------------------------------
    l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER('SQLGL', 'GL#', l_chart_of_accounts_id);
  
    If (l_flex_segment_delimiter Is Null)
    Then
      FND_MSG_PUB.Add;
      Raise AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
    End If;
  
    ----------------------------------------
    l_debug_info := l_debug_info_base || ' Check if p_ccid or p_segments is passed';
    ----------------------------------------
    If (p_ccid Is Not Null)
    Then
    
      ----------------------------------------
      l_debug_info := l_debug_info_base || ' Convert p_ccid into a segment array';
      ----------------------------------------
      If (Not FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#', l_chart_of_accounts_id, p_ccid, l_num_segments, l_exp_line_acct_segs_array))
      Then
        Return;
      End If;
    
    Elsif (p_segments Is Not Null And p_segments.count > 0)
    Then
    
      ----------------------------------------
      l_debug_info := l_debug_info_base || ' Convert p_segments into a segment array';
      ----------------------------------------
    
      If (l_num_segments Is Null)
      Then
        l_num_segments := p_segments.count;
      End If;
    
      -----------------------------------------------------
      l_debug_info := l_debug_info_base || ' Assign values to l_exp_line_acct_segs_array';
      -----------------------------------------------------
      For i In 1 .. l_num_segments
      Loop
        l_exp_line_acct_segs_array(i) := p_segments(i);
      End Loop;
    
    End If /* p_ccid is not null or p_segments is not null */
    ;
  
    If (p_exp_type_parameter_id Is Not Null)
    Then
      ------------------------------------------------------------------------
      l_debug_info := l_debug_info_base || ' calling AP_WEB_DB_EXPRPT_PKG.GetFlexConcactenated';
      ------------------------------------------------------------------------
      If (AP_WEB_DB_EXPRPT_PKG.GetFlexConcactenated(p_parameter_id => p_exp_type_parameter_id, p_FlexConcactenated => l_FlexConcactenated))
      Then
      
        If l_FlexConcactenated Is Not Null
        Then
        
          --------------------------------------------------------------
          l_debug_info := l_debug_info_base || ' Break Up Segments';
          --------------------------------------------------------------
          l_num_segments := FND_FLEX_EXT.Breakup_Segments(l_FlexConcactenated, l_flex_segment_delimiter, l_exp_type_template_array);
        End If;
      
      End If;
    
    End If; /* p_exp_type_parameter_id is not null */
  
    --------------------------------------------------------------
    l_debug_info := l_debug_info_base || ' Check Build Account Mode';
    --------------------------------------------------------------
    If (p_build_mode In (C_DEFAULT, C_DEFAULT_VALIDATE, C_BUILD_VALIDATE))
    Then
    
      -- Overlay the incoming segment values with the segment values
      -- defined in expense type template IF the incoming segment value
      -- is NULL.
    
      For i In 1 .. l_num_segments
      Loop
        -- If the incoming segment is not null, then keep this value, do nothing.
        If (p_segments Is Not Null And p_segments.EXISTS(i) And p_segments(i) Is Not Null)
        Then
        
          Null;
        
        Elsif (l_exp_type_template_array Is Not Null And l_exp_type_template_array.count > 0 And l_exp_type_template_array(i) Is Not Null)
        Then
        
          l_exp_line_acct_segs_array(i) := l_exp_type_template_array(i);
        
        Else
        
          /* If cost center is not defined on the expense type mask, override it from line
          * or header, if defined, in that order.  */
        
          If i = l_cost_center_seg_num
             And p_line_cost_center Is Not Null
          Then
            l_exp_line_acct_segs_array(i) := p_line_cost_center;
          
          Elsif i = l_cost_center_seg_num
                And p_cost_center Is Not Null
          Then
            l_exp_line_acct_segs_array(i) := p_cost_center;
          
          Elsif (p_build_mode In (C_DEFAULT, C_DEFAULT_VALIDATE))
          Then
            l_exp_line_acct_segs_array(i) := l_default_emp_segments(i);
          End If;
        
        End If; /* l_exp_type_template_array(i) IS NOT NULL */
      
      End Loop; /* 1..l_num_segments */
    
    End If; /* p_build_mode in (C_DEFAULT, C_DEFAULT_VALIDATE, C_BUILD_VALIDATE) */
  
  
  
  
  
  
    --  SZA customization - Start
    --------------------------------------------------------------
    l_debug_info := l_debug_info_base || ' SZA Report Header Id : ' || p_report_header_id || ' - Report Line Id : ' || p_report_line_id;
    --------------------------------------------------------------    
    --Insert Into cmlin_log (message) Values (l_debug_info);
  
    If (p_report_line_id Is Not Null)
    Then
      -- 1) Process the SZA Expense Type
      ------------------------------------------------------------------------
      l_debug_info := l_debug_info_base || ' calling SZA_AP_WEB_DB_EXPRPT_PKG.GetFlexConcactenated';
      ------------------------------------------------------------------------
      If (SZA_AP_WEB_DB_EXPRPT_PKG.GetFlexConcactenated(p_report_line_id => p_report_line_id, p_FlexConcactenated => l_FlexConcactenated))
      Then
        --------------------------------------------------------------
        l_debug_info := l_debug_info_base || ' SZA Report Header Id : ' || p_report_header_id || ' - ' || 'l_FlexConcactenated : ' || l_FlexConcactenated;
        --------------------------------------------------------------    
        -- Insert Into cmlin_log (message) Values (l_debug_info);
        If l_FlexConcactenated Is Not Null
        Then
        
          --------------------------------------------------------------
          l_debug_info := l_debug_info_base || ' SZA Report Header Id : ' || p_report_header_id || ' - ' || 'Break Up Segments';
          --------------------------------------------------------------
          l_num_segments := FND_FLEX_EXT.Breakup_Segments(l_FlexConcactenated, l_flex_segment_delimiter, l_exp_type_sza_array);
        End If;
      
        For i In 1 .. l_num_segments
        Loop
          -- If the incoming segment is not null, then keep this value, do nothing.
          If (l_exp_type_sza_array Is Not Null And l_exp_type_sza_array.count > 0 And l_exp_type_sza_array(i) Is Not Null)
          Then
            l_exp_line_acct_segs_array(i) := l_exp_type_sza_array(i);
            --------------------------------------------------------------
            l_debug_info := l_debug_info_base || ' SZA Report Header Id : ' || p_report_header_id || ' - ' || 'l_exp_type_sza_array' || i || ' : ' || l_exp_type_sza_array(i);
            --------------------------------------------------------------    
            -- Insert Into cmlin_log (message) Values (l_debug_info);
          End If; /* l_exp_type_sza_array(i) IS NOT NULL */
        
        End Loop; /* 1..l_num_segments */
      
      End If;
      
      -- 2) SZA special expense item
      -----------------------------------------------------
      l_debug_info := l_debug_info_base || ' SZA Report Header Id : ' || p_report_header_id || ' - ' || ' Get Expense Report Header - Attribute4';
      -----------------------------------------------------
      --Insert Into cmlin_log (message) Values (l_debug_info);

      Begin
        Select aerh.attribute4
          Into l_header_attribute4
          From ap_expense_report_headers aerh
         Where 1 = 1
               And aerh.report_header_id = p_report_header_id;
      Exception
        When Others Then
          l_header_attribute4 := '';
      End;
          --------------------------------------------------------------
          l_debug_info := l_debug_info_base || ' SZA Report Header Id : ' || p_report_header_id || ' - ' || ' special expense item' || ' : ' || l_header_attribute4;
          --------------------------------------------------------------    
          --Insert Into cmlin_log (message) Values (l_debug_info);

      If l_header_attribute4 Is Not Null
      Then
        -- 2.1) 
        --------------------------------------------------------------
        l_debug_info := l_debug_info_base || ' SZA Report Header Id : ' || p_report_header_id || ' - ' || ' l_header_attribute4 = ' || ' : ' || l_header_attribute4;
        --------------------------------------------------------------    
        --Insert Into cmlin_log (message) Values (l_debug_info);
        --------------------------------------------------------------
        l_debug_info := l_debug_info_base || ' SZA Report Header Id : ' || p_report_header_id || ' - ' || ' l_exp_line_acct_segs_array(5) = ' || ' : ' || l_exp_line_acct_segs_array(5);
        --------------------------------------------------------------    
        --Insert Into cmlin_log (message) Values (l_debug_info);
        If to_number(l_exp_line_acct_segs_array(5)) = 0
        Then
          l_exp_line_acct_segs_array(5) := l_header_attribute4;
          --------------------------------------------------------------
          l_debug_info := l_debug_info_base || ' 2.1.9  Report Header Id : ' || p_report_header_id || ' - ' || ' special expense item (inline if)' || ' : ' || l_exp_line_acct_segs_array(5);
          --------------------------------------------------------------    
          --Insert Into cmlin_log (message) Values (l_debug_info);
        End If;
        
        -- 2.2) Budget
        --------------------------------------------------------------
        l_debug_info := l_debug_info_base || ' processing the Budget Accounting ';
        --------------------------------------------------------------    
        --Insert Into cmlin_log (message) Values (l_debug_info);
        
        ------------------------------------------------------------------------
        l_debug_info := l_debug_info_base || ' calling SZA_AP_WEB_DB_EXPRPT_PKG.GetBudgetFlexConcactenated';
        ------------------------------------------------------------------------
        If (SZA_AP_WEB_DB_EXPRPT_PKG.GetBudgetFlexConcactenated(p_report_line_id => p_report_line_id
                                                              , p_FlexConcactenated => l_b_FlexConcactenated)
           )
        Then
          --------------------------------------------------------------
          l_debug_info := l_debug_info_base || 'calling SZA_AP_WEB_DB_EXPRPT_PKG.GetBudgetFlexConcactenated - ' || 'l_b_FlexConcactenated : ' || l_b_FlexConcactenated;
          --------------------------------------------------------------    
          --Insert Into cmlin_log (message) Values (l_debug_info);
    
          If l_b_FlexConcactenated Is Not Null
          Then          
            --------------------------------------------------------------
            l_debug_info := l_debug_info_base || ' - ' || 'Break Up Segments';
            --------------------------------------------------------------
            --Insert Into cmlin_log (message) Values (l_debug_info);
            
            l_b_num_segments := FND_FLEX_EXT.Breakup_Segments(l_b_FlexConcactenated, l_flex_segment_delimiter, l_b_exp_line_acct_segs_array);
            --------------------------------------------------------------
            l_debug_info := l_debug_info_base || ' - ' || 'Break Up Segments - l_b_num_segments' || l_b_num_segments;
            --------------------------------------------------------------
            --Insert Into cmlin_log (message) Values (l_debug_info);
          End If;  

                     
          If (/*l_b_exp_line_acct_segs_array Is Not Null 
              And l_b_exp_line_acct_segs_array.count = 5 
              And */to_number(l_b_exp_line_acct_segs_array(5)) = 0)
          Then
              l_b_exp_line_acct_segs_array(5) := l_header_attribute4;
              --------------------------------------------------------------
              l_debug_info := l_debug_info_base || ' 2.2.9 l_b_exp_line_acct_segs_array(5):' || l_b_exp_line_acct_segs_array(5);
              --------------------------------------------------------------    
              --Insert Into cmlin_log (message) Values (l_debug_info);
          End If;
            
          l_b_concatenated_segments := FND_FLEX_EXT.concatenate_segments(n_segments => l_b_num_segments
                                                                       , segments => l_b_exp_line_acct_segs_array
                                                                       , delimiter => l_flex_segment_delimiter);
          --------------------------------------------------------------
          l_debug_info := l_debug_info_base || ' l_b_concatenated_segments:' || l_b_concatenated_segments;
          --------------------------------------------------------------    
          --Insert Into cmlin_log (message) Values (l_debug_info);

          Update ap_expense_report_lines_all aerl
             Set aerl.attribute13 = l_b_concatenated_segments
           Where aerl.report_line_id = p_report_line_id;
          
        End If;       
 
      End If; --If l_header_attribute4 Is Not Null Then
      
      -- 3) Process the SZA OIE Prepayment
      -----------------------------------------------------
      l_debug_info := l_debug_info_base || ' SZA Get Expense Report Header - Attribute13';
      -----------------------------------------------------
      --Insert Into cmlin_log (message) Values (l_debug_info);

      Begin
        Select aerh.attribute13
          Into l_header_attribute13
          From ap_expense_report_headers aerh
         Where 1 = 1
               And aerh.report_header_id = p_report_header_id;
      Exception
        When Others Then
          l_header_attribute13 := '';
      End;
      --------------------------------------------------------------
      l_debug_info := l_debug_info_base || ' SZA l_header_attribute13 = ' || l_header_attribute13;
      --------------------------------------------------------------    
      --Insert Into cmlin_log (message) Values (l_debug_info);
    
      If l_header_attribute13 = 'Y'
      Then
        ------------------------------------------------------------------------
        l_debug_info := l_debug_info_base || ' calling SZA_AP_WEB_DB_EXPRPT_PKG.GetFlexConcactenated';
        ------------------------------------------------------------------------
        --Insert Into cmlin_log (message) Values (l_debug_info);
                
        l_FlexConcactenated := Null;
        If (SZA_AP_WEB_DB_EXPRPT_PKG.GetVendorFlexConcactenated(p_report_line_id => p_report_line_id, p_FlexConcactenated => l_FlexConcactenated))
        Then
          --------------------------------------------------------------
          l_debug_info := l_debug_info_base || ' SZA l_FlexConcactenated : ' || l_FlexConcactenated;
          --------------------------------------------------------------    
          --Insert Into cmlin_log (message) Values (l_debug_info);
          
          If l_FlexConcactenated Is Not Null
          Then
          
            --------------------------------------------------------------
            l_debug_info := l_debug_info_base || ' SZA Break Up Segments';
            --------------------------------------------------------------
            --Insert Into cmlin_log (message) Values (l_debug_info);
            
            l_num_segments := FND_FLEX_EXT.Breakup_Segments(l_FlexConcactenated, l_flex_segment_delimiter, l_vendor_sza_array);
          End If;
         
         -- insert into test values(0,1,'l_FlexConcactenated :'||l_FlexConcactenated,null);
     
          For i In 1 .. l_num_segments
          Loop
            -- If the incoming segment is not null, then keep this value, do nothing.
            If (l_vendor_sza_array Is Not Null And l_vendor_sza_array.count > 0 And l_vendor_sza_array(i) Is Not Null)
            Then
              l_exp_line_acct_segs_array(i) := l_vendor_sza_array(i);
              --------------------------------------------------------------
              l_debug_info := l_debug_info_base || ' SZA l_vendor_sza_array' || i || ' : ' || l_vendor_sza_array(i);
              --------------------------------------------------------------    
              --Insert Into cmlin_log (message) Values (l_debug_info);
              
            End If; /* l_vendor_sza_array(i) IS NOT NULL */
          
          End Loop; /* 1..l_num_segments */
        
        End If;
      End If; -- l_header_attribute13
      
       -------------------------------------------------------------------------------------------------
    -- add by xiezili 2012-06-12
    -- poupse : change prepay account build logic.
    -------------------------------------------------------------------------------------------------
          Begin
        Select saer.attribute3
          Into lv_is_prepay
          From ap_expense_report_headers aerh,
               sza.sza_ap_expense_reports_all saer
         Where aerh.expense_report_id  = saer.expense_report_id
               And aerh.report_header_id = p_report_header_id;
      Exception
        When Others Then
          lv_is_prepay := '';
      End;
      --------------------------------------------------------------
      l_debug_info := l_debug_info_base || ' SZA lv_is_prepay = ' || lv_is_prepay;
      --------------------------------------------------------------    
      --Insert Into cmlin_log (message) Values (l_debug_info);
    
      If lv_is_prepay = 'Y'
      Then
        ------------------------------------------------------------------------
        l_debug_info := l_debug_info_base || ' calling SZA_AP_WEB_DB_EXPRPT_PKG.GetFlexConcactenated';
        ------------------------------------------------------------------------
        --Insert Into cmlin_log (message) Values (l_debug_info);
                
        l_FlexConcactenated := Null;
        If (SZA_AP_WEB_DB_EXPRPT_PKG.GetFlexConcactenated(p_report_line_id => p_report_line_id, p_FlexConcactenated => l_FlexConcactenated))
        Then
          --------------------------------------------------------------
          l_debug_info := l_debug_info_base || ' SZA l_FlexConcactenated : ' || l_FlexConcactenated;
          --------------------------------------------------------------    
          --Insert Into cmlin_log (message) Values (l_debug_info);
          
          If l_FlexConcactenated Is Not Null
          Then
          
            --------------------------------------------------------------
            l_debug_info := l_debug_info_base || ' SZA Break Up Segments';
            --------------------------------------------------------------
            --Insert Into cmlin_log (message) Values (l_debug_info);
            
            l_num_segments := FND_FLEX_EXT.Breakup_Segments(l_FlexConcactenated, l_flex_segment_delimiter, l_vendor_sza_array);
          End If;
         
         -- insert into test values(0,1,'l_FlexConcactenated :'||l_FlexConcactenated,null);
     
          For i In 1 .. l_num_segments
          Loop
            -- If the incoming segment is not null, then keep this value, do nothing.
            If (l_vendor_sza_array Is Not Null And l_vendor_sza_array.count > 0 And l_vendor_sza_array(i) Is Not Null)
            Then
              l_exp_line_acct_segs_array(i) := l_vendor_sza_array(i);
              --------------------------------------------------------------
              l_debug_info := l_debug_info_base || ' SZA l_vendor_sza_array' || i || ' : ' || l_vendor_sza_array(i);
              --------------------------------------------------------------    
              --Insert Into cmlin_log (message) Values (l_debug_info);
              
            End If; /* l_vendor_sza_array(i) IS NOT NULL */
          
          End Loop; /* 1..l_num_segments */
        
        End If;
      End If; -- lv_is_prepay
    

    
    End If; -- p_exp_type_parameter_id is not null 
    
    
    
   
    
    --  SZA customization - End
  
    --------------------------------------------------------------
    l_debug_info := l_debug_info_base || ' SZA Check Build Account Mode - p_build_mode: ' || p_build_mode;
    --------------------------------------------------------------
    --Insert Into cmlin_log (message) Values (l_debug_info);
    If (p_build_mode In (C_DEFAULT_VALIDATE, C_BUILD_VALIDATE, C_VALIDATE))
    Then
    
      --------------------------------------------------------------
      l_debug_info := l_debug_info_base || ' Build Account Mode contains VALIDATE';
      --------------------------------------------------------------
    
      --------------------------------------------------------------
      l_debug_info := l_debug_info_base || ' Retrieve new ccid';
      --------------------------------------------------------------
      -- Work around for bug 1569108
      l_concatenated_segments := FND_FLEX_EXT.concatenate_segments(l_num_segments, l_exp_line_acct_segs_array, l_flex_segment_delimiter);
     
      
      
      
      ------------------------------------------------------------------------
      l_debug_info := l_debug_info_base || ' calling FND_FLEX_KEYVAL.validate_segs';
      ------------------------------------------------------------------------
      If (FND_FLEX_KEYVAL.validate_segs('CREATE_COMBINATION', 'SQLGL', 'GL#', l_chart_of_accounts_id, l_concatenated_segments))
      Then
        p_new_ccid := FND_FLEX_KEYVAL.combination_id;
        null;
      Else
      
        p_return_error_message := FND_FLEX_KEYVAL.error_message;
        FND_MESSAGE.set_encoded(FND_FLEX_KEYVAL.encoded_error_message);
        fnd_msg_pub.add();
      
      End If; /* FND_FLEX_KEYVAL.validate_segs */
    
    End If; /* p_build_mode in (C_DEFAULT_VALIDATE, C_BUILD_VALIDATE, C_VALIDATE) */
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Assign values to p_new_segments';
    -----------------------------------------------------
    p_new_segments := AP_OIE_KFF_SEGMENTS_T('');
    p_new_segments.extend(l_num_segments);
    For i In 1 .. l_num_segments
    Loop
      p_new_segments(i) := l_exp_line_acct_segs_array(i);
      --------------------------------------------------------------
      l_debug_info := l_debug_info_base || ' SZA Assign values to p_new_segments p_new_segments(i)' || i || ' : ' || p_new_segments(i);
      --------------------------------------------------------------    
      --Insert Into cmlin_log (message) Values (l_debug_info);
    End Loop;
  
  Exception
    When Others Then
      AP_WEB_DB_UTIL_PKG.RaiseException('BuildAccount');
      APP_EXCEPTION.RAISE_EXCEPTION;
    
  End BuildAccount;

  Procedure BuildDistProjectAccount(p_report_header_id       In Number
                                   ,p_report_line_id         In Number
                                   ,p_report_distribution_id In Number
                                   ,p_exp_type_parameter_id  In Number
                                   ,p_new_segments           Out Nocopy AP_OIE_KFF_SEGMENTS_T
                                   ,p_new_ccid               Out Nocopy Number
                                   ,p_return_error_message   Out Nocopy Varchar2
                                   ,p_return_status          Out Nocopy Varchar2) Is
  
    Cursor pa_cur Is
      Select aerh.employee_id
            ,aerh.flex_concatenated           header_cost_center
            ,aerh.default_currency_code       reimbursement_currency_code
            ,aerh.week_end_date
            ,aerh.attribute_category          header_attribute_category
            ,aerh.attribute1                  header_attribute1
            ,aerh.attribute2                  header_attribute2
            ,aerh.attribute3                  header_attribute3
            ,aerh.attribute4                  header_attribute4
            ,aerh.attribute5                  header_attribute5
            ,aerh.attribute6                  header_attribute6
            ,aerh.attribute7                  header_attribute7
            ,aerh.attribute8                  header_attribute8
            ,aerh.attribute9                  header_attribute9
            ,aerh.attribute10                 header_attribute10
            ,aerh.attribute11                 header_attribute11
            ,aerh.attribute12                 header_attribute12
            ,aerh.attribute13                 header_attribute13
            ,aerh.attribute14                 header_attribute14
            ,aerh.attribute15                 header_attribute15
            ,aerl.start_expense_date
            ,dist.amount
            ,aerl.credit_card_trx_id
            ,aerl.expenditure_item_date
            ,aerl.expenditure_type
            ,aerl.pa_quantity
            ,dist.expenditure_organization_id
            ,aerl.adjustment_reason
            ,aerl.category_code
            ,aerl.attribute_category          line_attribute_category
            ,aerl.attribute1                  line_attribute1
            ,aerl.attribute2                  line_attribute2
            ,aerl.attribute3                  line_attribute3
            ,aerl.attribute4                  line_attribute4
            ,aerl.attribute5                  line_attribute5
            ,aerl.attribute6                  line_attribute6
            ,aerl.attribute7                  line_attribute7
            ,aerl.attribute8                  line_attribute8
            ,aerl.attribute9                  line_attribute9
            ,aerl.attribute10                 line_attribute10
            ,aerl.attribute11                 line_attribute11
            ,aerl.attribute12                 line_attribute12
            ,aerl.attribute13                 line_attribute13
            ,aerl.attribute14                 line_attribute14
            ,aerl.attribute15                 line_attribute15
            ,dist.cost_center                 line_flex_concat
            ,aerl.ap_validation_error
            ,dist.project_id
            ,dist.task_id
            ,dist.award_id
            ,s.base_currency_code
            ,s.default_exchange_rate_type
            ,sob.chart_of_accounts_id
            ,erp.pa_expenditure_type          new_expenditure_type
        From ap_expense_report_headers aerh
            ,ap_expense_report_lines   aerl
            ,ap_exp_report_dists       dist
            ,ap_system_parameters      s
            ,fnd_currencies_vl         c
            ,gl_sets_of_books          sob
            ,ap_expense_report_params  erp
       Where c.currency_code = s.base_currency_code
             And sob.set_of_books_id = s.set_of_books_id
             And erp.parameter_id = p_exp_type_parameter_id
             And dist.report_distribution_id = p_report_distribution_id
             And dist.report_line_id = aerl.report_line_id
             And aerh.report_header_id = aerl.report_header_id;
  
    ln_default_exchange_rate      Number;
    ln_acct_raw_cost              Number;
    ln_vendor_id                  Number;
    ln_award_set_id               Number;
    lv_expense_report_cost_center AP_WEB_DB_EXPRPT_PKG.expHdr_flexConcat;
    lv_procedure_billable_flag    Varchar2(200);
    lv_msg_type                   Varchar2(2000);
    l_pa_CCID                     gl_code_combinations.code_combination_id%Type;
    lv_concat_segs                Varchar2(2000);
    lv_concat_ids                 Varchar2(2000);
    lv_concat_description         Varchar2(2000);
    l_segments                    FND_FLEX_EXT.SEGMENTARRAY;
    ln_segment_count              Number := 0;
    lv_flex_segment_delimiter     Varchar2(1);
    lb_gms_accounting_created     Boolean := False;
  
    pa_rec             pa_cur%Rowtype;
    l_debug_info       Varchar2(200);
    l_debug_info_base Varchar2(4000);
    
    l_default_emp_ccid AP_WEB_DB_EXPRPT_PKG.expHdr_employeeCCID;
  Begin
  
    If p_report_header_id Is Null
       Or p_report_line_id Is Null
       Or p_report_distribution_id Is Null
       Or p_exp_type_parameter_id Is Null
    Then
      p_new_ccid      := to_number(Null);
      p_return_status := 'ERROR';
      Return;
    End If;
  
    If (AP_WEB_CUS_ACCTG_PKG.BuildDistProjectAccount(p_report_header_id       => p_report_header_id
                                                    ,p_report_line_id         => p_report_line_id
                                                    ,p_report_distribution_id => p_report_distribution_id
                                                    ,p_exp_type_parameter_id  => p_exp_type_parameter_id
                                                    ,p_new_segments           => p_new_segments
                                                    ,p_new_ccid               => p_new_ccid
                                                    ,p_return_error_message   => p_return_error_message
                                                    ,p_return_status          => p_return_status))
    Then
    
      --------------------------------------------------------------------
      l_debug_info := l_debug_info_base || ' Custom BuildDistProjectAccount';
      --------------------------------------------------------------------
    
      Return;
    
    End If;
  
    p_return_status := 'SUCCESS';
  
    Open pa_cur;
    Fetch pa_cur
      Into pa_rec;
    If pa_cur%Notfound
    Then
      Close pa_cur;
      p_new_ccid      := to_number(Null);
      p_return_status := 'ERROR';
      Return;
    End If;
    Close pa_cur;
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  vendor ID if exists for this employee';
    -----------------------------------------------------
    If (Not AP_WEB_DB_AP_INT_PKG.GetVendorID(pa_rec.employee_id, ln_vendor_id))
    Then
      ln_vendor_id := Null;
    End If; /* GetVendorID */
  
    -- For PATC: Get the default exchange rate for the week_end_date reimbursement currency/functional currency
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' AP_UTILITIES_PKG.get_exchange_rate';
    -----------------------------------------------------
    ln_default_exchange_rate := AP_UTILITIES_PKG.get_exchange_rate(pa_rec.base_currency_code, pa_rec.reimbursement_currency_code, pa_rec.default_exchange_rate_type, pa_rec.week_end_date, 'ValidatePATransaction');
  
    -- Calculate the receipt amount in the functional currency
    ln_acct_raw_cost := Null;
    If ln_default_exchange_rate Is Not Null
       And ln_default_exchange_rate <> 0
    Then
      ln_acct_raw_cost := AP_WEB_UTILITIES_PKG.OIE_ROUND_CURRENCY(pa_rec.amount / ln_default_exchange_rate, pa_rec.base_currency_code);
    End If;
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Call PATC to get billable flag value';
    -----------------------------------------------------
    AP_WEB_PROJECT_PKG.ValidatePATransaction(p_project_id          => pa_rec.project_id
                                            ,p_task_id             => pa_rec.task_id
                                            ,p_ei_date             => pa_rec.expenditure_item_date
                                            ,p_expenditure_type    => pa_rec.new_expenditure_type
                                            ,p_non_labor_resource  => Null
                                            ,p_person_id           => pa_rec.employee_id
                                            ,p_quantity            => Null
                                            ,p_denom_currency_code => pa_rec.reimbursement_currency_code
                                            ,p_acct_currency_code  => pa_rec.base_currency_code
                                            ,p_denom_raw_cost      => pa_rec.amount
                                            ,p_acct_raw_cost       => ln_acct_raw_cost
                                            ,p_acct_rate_type      => pa_rec.default_exchange_rate_type
                                            ,p_acct_rate_date      => pa_rec.week_end_date
                                            ,p_acct_exchange_rate  => ln_default_exchange_rate
                                            ,p_transfer_ei         => Null
                                            ,p_incurred_by_org_id  => pa_rec.expenditure_organization_id
                                            ,p_nl_resource_org_id  => Null
                                            ,p_transaction_source  => Null
                                            ,p_calling_module      => 'SelfService'
                                            ,p_vendor_id           => ln_vendor_id
                                            ,p_entered_by_user_id  => Null
                                            ,p_attribute_category  => pa_rec.line_attribute_category
                                            ,p_attribute1          => pa_rec.line_attribute1
                                            ,p_attribute2          => pa_rec.line_attribute2
                                            ,p_attribute3          => pa_rec.line_attribute3
                                            ,p_attribute4          => pa_rec.line_attribute4
                                            ,p_attribute5          => pa_rec.line_attribute5
                                            ,p_attribute6          => pa_rec.line_attribute6
                                            ,p_attribute7          => pa_rec.line_attribute7
                                            ,p_attribute8          => pa_rec.line_attribute8
                                            ,p_attribute9          => pa_rec.line_attribute9
                                            ,p_attribute10         => pa_rec.line_attribute10
                                            ,p_attribute11         => pa_rec.line_attribute11
                                            ,p_attribute12         => pa_rec.line_attribute12
                                            ,p_attribute13         => pa_rec.line_attribute13
                                            ,p_attribute14         => pa_rec.line_attribute14
                                            ,p_attribute15         => pa_rec.line_attribute15
                                            ,p_msg_type            => lv_msg_type
                                            ,p_msg_data            => p_return_error_message
                                            ,p_billable_flag       => lv_procedure_billable_flag);
  
    If (p_return_error_message Is Not Null)
       And (lv_msg_type = AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeError)
    Then
      /* validation failed, we cannot generate the projects CCID */
      p_new_ccid      := to_number(Null);
      p_return_status := 'VALIDATION_ERROR';
      Return;
    Else
      /* For some reason the projects API returns error even though validation is succesful.
      * When the message data is also provided then the validation actually failed. Since these
      * same variables are used elsewhere resetting them to null when validation was succesful */
      lv_msg_type            := Null;
      p_return_error_message := Null;
    End If;
  
    -- Get Award Set ID before calling A/C generator
    ln_award_set_id := Null;
  
    If (GMS_OIE_INT_PKG.IsGrantsEnabled() And pa_rec.award_id Is Not Null)
    Then
      -----------------------------------------------------
      l_debug_info := l_debug_info_base || ' GMS_OIE_INT_PKG.CreateACGenADL';
      -----------------------------------------------------
      ln_award_set_id           := GMS_OIE_INT_PKG.CreateACGenADL(p_award_id => pa_rec.award_id, p_project_id => pa_rec.project_id, p_task_id => pa_rec.task_id);
      lb_gms_accounting_created := True;
    End If;
  
    If (pa_rec.line_flex_concat Is Not Null)
    Then
      lv_expense_report_cost_center := pa_rec.line_flex_concat;
    Else
      lv_expense_report_cost_center := pa_rec.header_cost_center;
    End If;
  
    --bug 4629320
    If AP_WEB_DB_EXPRPT_PKG.GetDefaultEmpCCID(pa_rec.employee_id, l_default_emp_ccid)
    Then
      Null;
    End If;
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' pa_acc_gen_wf_pkg.ap_er_generate_account';
    -----------------------------------------------------
    If (Not pa_acc_gen_wf_pkg.ap_er_generate_account(p_project_id                  => pa_rec.project_id
                                                    ,p_task_id                     => pa_rec.task_id
                                                    ,p_expenditure_type            => pa_rec.new_expenditure_type
                                                    ,p_vendor_id                   => ln_vendor_id
                                                    ,p_expenditure_organization_id => pa_rec.expenditure_organization_id
                                                    ,
                                                     
                                                     p_expenditure_item_date   => pa_rec.expenditure_item_date
                                                    ,p_billable_flag           => lv_procedure_billable_flag
                                                    ,p_chart_of_accounts_id    => pa_rec.chart_of_accounts_id
                                                    ,p_calling_module          => 'SelfService'
                                                    ,p_employee_id             => pa_rec.employee_id
                                                    ,p_employee_ccid           => l_default_emp_ccid
                                                    ,p_expense_type            => p_exp_type_parameter_id
                                                    ,p_expense_cc              => lv_expense_report_cost_center
                                                    ,p_attribute_category      => pa_rec.header_attribute_category
                                                    ,p_attribute1              => pa_rec.header_attribute1
                                                    ,p_attribute2              => pa_rec.header_attribute2
                                                    ,p_attribute3              => pa_rec.header_attribute3
                                                    ,p_attribute4              => pa_rec.header_attribute4
                                                    ,p_attribute5              => pa_rec.header_attribute5
                                                    ,p_attribute6              => pa_rec.header_attribute6
                                                    ,p_attribute7              => pa_rec.header_attribute7
                                                    ,p_attribute8              => pa_rec.header_attribute8
                                                    ,p_attribute9              => pa_rec.header_attribute9
                                                    ,p_attribute10             => pa_rec.header_attribute10
                                                    ,p_attribute11             => pa_rec.header_attribute11
                                                    ,p_attribute12             => pa_rec.header_attribute12
                                                    ,p_attribute13             => pa_rec.header_attribute13
                                                    ,p_attribute14             => pa_rec.header_attribute14
                                                    ,p_attribute15             => pa_rec.header_attribute15
                                                    ,p_line_attribute_category => pa_rec.line_attribute_category
                                                    ,p_line_attribute1         => pa_rec.line_attribute1
                                                    ,p_line_attribute2         => pa_rec.line_attribute2
                                                    ,p_line_attribute3         => pa_rec.line_attribute3
                                                    ,p_line_attribute4         => pa_rec.line_attribute4
                                                    ,p_line_attribute5         => pa_rec.line_attribute5
                                                    ,p_line_attribute6         => pa_rec.line_attribute6
                                                    ,p_line_attribute7         => pa_rec.line_attribute7
                                                    ,p_line_attribute8         => pa_rec.line_attribute8
                                                    ,p_line_attribute9         => pa_rec.line_attribute9
                                                    ,p_line_attribute10        => pa_rec.line_attribute10
                                                    ,p_line_attribute11        => pa_rec.line_attribute11
                                                    ,p_line_attribute12        => pa_rec.line_attribute12
                                                    ,p_line_attribute13        => pa_rec.line_attribute13
                                                    ,p_line_attribute14        => pa_rec.line_attribute14
                                                    ,p_line_attribute15        => pa_rec.line_attribute15
                                                    ,x_return_ccid             => l_pa_ccid
                                                    ,x_concat_segs             => lv_concat_segs
                                                    ,x_concat_ids              => lv_concat_ids
                                                    ,x_concat_descrs           => lv_concat_description
                                                    ,x_error_message           => p_return_error_message
                                                    ,x_award_set_id            => ln_award_set_id))
    Then
    
      If (p_return_error_message Is Not Null)
      Then
        /* could not generate the projects CCID */
        p_new_ccid      := to_number(Null);
        p_return_status := 'GENERATION_ERROR';
        Return;
      
      End If;
    
    End If; /*  pa_acc_gen_wf_pkg.ap_er_generate_account */
  
    If (lb_gms_accounting_created = True)
    Then
      -----------------------------------------------------
      l_debug_info := l_debug_info_base || ' gms_oie_int_pkg.DeleteACGenADL';
      -----------------------------------------------------
      If gms_oie_int_pkg.DeleteACGenADL(ln_award_set_id)
      Then
        Null;
      End If;
    End If;
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Get  segment delimeter';
    -----------------------------------------------------
    lv_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER('SQLGL', 'GL#', pa_rec.chart_of_accounts_id);
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Break up segments';
    -----------------------------------------------------
    ln_segment_count := FND_FLEX_EXT.Breakup_Segments(lv_concat_segs, lv_flex_segment_delimiter, l_segments);
  
    -----------------------------------------------------
    l_debug_info := l_debug_info_base || ' Assign values to p_new_segments';
    -----------------------------------------------------
    p_new_segments := AP_OIE_KFF_SEGMENTS_T('');
    p_new_segments.extend(ln_segment_count);
    For i In 1 .. ln_segment_count
    Loop
      p_new_segments(i) := l_segments(i);
    End Loop;
  
    -- create account if don't already exist
    If (l_pa_ccid = -1)
    Then
    
      -----------------------------------------------------
      l_debug_info := l_debug_info_base || ' Validate segments';
      -----------------------------------------------------
      If (FND_FLEX_KEYVAL.validate_segs('CREATE_COMBINATION', 'SQLGL', 'GL#', pa_rec.chart_of_accounts_id, lv_concat_segs))
      Then
        l_pa_ccid := FND_FLEX_KEYVAL.combination_id;
      Else
        p_return_error_message := FND_FLEX_KEYVAL.error_message;
        /* could not generate the projects CCID */
        p_new_ccid      := to_number(Null);
        p_return_status := 'GENERATION_ERROR';
        Return;
      End If; /* FND_FLEX_KEYVAL.validate_segs */
    
    End If; /* l_pa_ccid = -1 */
  
    If (l_pa_ccid Is Null Or l_pa_ccid = -1)
    Then
      p_new_ccid      := to_number(Null);
      p_return_status := 'GENERATION_ERROR';
    Else
      p_new_ccid := l_pa_ccid;
    End If;
  
  Exception
    When Others Then
      /* could not generate the projects CCID */
      p_new_ccid      := to_number(Null);
      p_return_status := 'ERROR';
    
  End BuildDistProjectAccount;

  Procedure ValidateProjectAccounting(p_report_line_id       In Number
                                     ,p_web_parameter_id     In Number
                                     ,p_project_id           In Number
                                     ,p_task_id              In Number
                                     ,p_award_id             In Number
                                     ,p_award_number         In Varchar2
                                     ,p_expenditure_org_id   In Number
                                     ,p_amount               In Number
                                     ,p_return_error_message Out Nocopy Varchar2
                                     ,p_msg_count            Out Nocopy Number
                                     ,p_msg_data             Out Nocopy Varchar2) Is
  
    Cursor pa_cur Is
      Select aerh.employee_id
            ,aerh.flex_concatenated       header_cost_center
            ,aerh.default_currency_code   reimbursement_currency_code
            ,aerh.week_end_date
            ,aerh.attribute_category      header_attribute_category
            ,aerh.attribute1              header_attribute1
            ,aerh.attribute2              header_attribute2
            ,aerh.attribute3              header_attribute3
            ,aerh.attribute4              header_attribute4
            ,aerh.attribute5              header_attribute5
            ,aerh.attribute6              header_attribute6
            ,aerh.attribute7              header_attribute7
            ,aerh.attribute8              header_attribute8
            ,aerh.attribute9              header_attribute9
            ,aerh.attribute10             header_attribute10
            ,aerh.attribute11             header_attribute11
            ,aerh.attribute12             header_attribute12
            ,aerh.attribute13             header_attribute13
            ,aerh.attribute14             header_attribute14
            ,aerh.attribute15             header_attribute15
            ,aerl.start_expense_date
            ,aerl.end_expense_date
            ,aerl.credit_card_trx_id
            ,aerl.expenditure_item_date
            ,aerl.expenditure_type
            ,aerl.pa_quantity
            ,aerl.adjustment_reason
            ,aerl.category_code
            ,aerl.attribute_category      line_attribute_category
            ,aerl.attribute1              line_attribute1
            ,aerl.attribute2              line_attribute2
            ,aerl.attribute3              line_attribute3
            ,aerl.attribute4              line_attribute4
            ,aerl.attribute5              line_attribute5
            ,aerl.attribute6              line_attribute6
            ,aerl.attribute7              line_attribute7
            ,aerl.attribute8              line_attribute8
            ,aerl.attribute9              line_attribute9
            ,aerl.attribute10             line_attribute10
            ,aerl.attribute11             line_attribute11
            ,aerl.attribute12             line_attribute12
            ,aerl.attribute13             line_attribute13
            ,aerl.attribute14             line_attribute14
            ,aerl.attribute15             line_attribute15
            ,aerl.ap_validation_error
            ,s.base_currency_code
            ,s.default_exchange_rate_type
            ,sob.chart_of_accounts_id
            ,erp.pa_expenditure_type      new_expenditure_type
        From ap_expense_report_headers_all aerh
            ,ap_expense_report_lines_all   aerl
            ,ap_system_parameters_all      s
            ,fnd_currencies_vl             c
            ,gl_sets_of_books              sob
            ,ap_expense_report_params_all  erp
       Where c.currency_code = s.base_currency_code
             And sob.set_of_books_id = s.set_of_books_id
             And erp.parameter_id = p_web_parameter_id
             And aerh.report_header_id = aerl.report_header_id
             And aerl.report_line_id = p_report_line_id;
  
    pa_rec                     pa_cur%Rowtype;
    lv_msg_type                Varchar2(2000);
    lv_procedure_billable_flag Varchar2(200);
    ln_vendor_id               Number;
    ln_default_exchange_rate   Number;
    ln_acct_raw_cost           Number;
    l_errors                   AP_WEB_UTILITIES_PKG.expError;
    l_receipts_errors          AP_WEB_UTILITIES_PKG.receipt_error_stack;
    I                          Integer;
  Begin
    If p_report_line_id Is Null
       Or p_web_parameter_id Is Null
    Then
      p_return_error_message := 'ERROR';
      Return;
    End If;
  
    -- Bug: 7176464
    If (AP_WEB_CUS_ACCTG_PKG.CustomValidateProjectDist(p_report_line_id, p_web_parameter_id, p_project_id, p_task_id, p_award_id, p_expenditure_org_id, p_amount, p_return_error_message))
    Then
      -- Custom Validate Project Allocations
      If (p_return_error_message Is Not Null)
      Then
      
        AP_WEB_UTILITIES_PKG.AddExpError(l_errors, p_return_error_message, lv_msg_type, 'PATC', 1, AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
        p_msg_count := 1;
        p_msg_data  := p_return_error_message;
        fnd_msg_pub.count_and_get(p_count => p_msg_count, p_data => p_msg_data);
        -- Bug 7497991 Commenting Return Statement to continue ValidationS
        -- return;
      End If;
    End If;
  
    Open pa_cur;
  
    Fetch pa_cur
      Into pa_rec;
    If pa_cur%Notfound
    Then
      Close pa_cur;
      p_return_error_message := 'ERROR';
      Return;
    End If;
    Close pa_cur;
  
    AP_WEB_UTILITIES_PKG.InitMessages(1, l_receipts_errors);
  
    If (Not AP_WEB_DB_AP_INT_PKG.GetVendorID(pa_rec.employee_id, ln_vendor_id))
    Then
      ln_vendor_id := Null;
    End If; /* GetVendorID */
  
    ln_default_exchange_rate := AP_UTILITIES_PKG.get_exchange_rate(pa_rec.base_currency_code, pa_rec.reimbursement_currency_code, pa_rec.default_exchange_rate_type, pa_rec.week_end_date, 'ValidatePATransaction');
  
    -- Calculate the receipt amount in the functional currency
    ln_acct_raw_cost := Null;
    If ln_default_exchange_rate Is Not Null
       And ln_default_exchange_rate <> 0
    Then
      ln_acct_raw_cost := AP_WEB_UTILITIES_PKG.OIE_ROUND_CURRENCY(p_amount / ln_default_exchange_rate, pa_rec.base_currency_code);
    End If;
  
    AP_WEB_PROJECT_PKG.ValidatePATransaction(p_project_id          => p_project_id
                                            ,p_task_id             => p_task_id
                                            ,p_ei_date             => pa_rec.start_expense_date
                                            ,p_expenditure_type    => pa_rec.new_expenditure_type
                                            ,p_non_labor_resource  => Null
                                            ,p_person_id           => pa_rec.employee_id
                                            ,p_quantity            => Null
                                            ,p_denom_currency_code => pa_rec.reimbursement_currency_code
                                            ,p_acct_currency_code  => pa_rec.base_currency_code
                                            ,p_denom_raw_cost      => p_amount
                                            ,p_acct_raw_cost       => ln_acct_raw_cost
                                            ,p_acct_rate_type      => pa_rec.default_exchange_rate_type
                                            ,p_acct_rate_date      => pa_rec.week_end_date
                                            ,p_acct_exchange_rate  => ln_default_exchange_rate
                                            ,p_transfer_ei         => Null
                                            ,p_incurred_by_org_id  => p_expenditure_org_id
                                            ,p_nl_resource_org_id  => Null
                                            ,p_transaction_source  => Null
                                            ,p_calling_module      => 'SelfService'
                                            ,p_vendor_id           => ln_vendor_id
                                            ,p_entered_by_user_id  => Null
                                            ,p_attribute_category  => pa_rec.line_attribute_category
                                            ,p_attribute1          => pa_rec.line_attribute1
                                            ,p_attribute2          => pa_rec.line_attribute2
                                            ,p_attribute3          => pa_rec.line_attribute3
                                            ,p_attribute4          => pa_rec.line_attribute4
                                            ,p_attribute5          => pa_rec.line_attribute5
                                            ,p_attribute6          => pa_rec.line_attribute6
                                            ,p_attribute7          => pa_rec.line_attribute7
                                            ,p_attribute8          => pa_rec.line_attribute8
                                            ,p_attribute9          => pa_rec.line_attribute9
                                            ,p_attribute10         => pa_rec.line_attribute10
                                            ,p_attribute11         => pa_rec.line_attribute11
                                            ,p_attribute12         => pa_rec.line_attribute12
                                            ,p_attribute13         => pa_rec.line_attribute13
                                            ,p_attribute14         => pa_rec.line_attribute14
                                            ,p_attribute15         => pa_rec.line_attribute15
                                            ,p_msg_type            => lv_msg_type
                                            ,p_msg_data            => p_return_error_message
                                            ,p_billable_flag       => lv_procedure_billable_flag);
  
    If (p_return_error_message Is Not Null And lv_msg_type = AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeError)
    Then
      AP_WEB_UTILITIES_PKG.AddExpError(l_errors, p_return_error_message, lv_msg_type, 'PATC', 1, AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
    Else
      -- Bug: 6936055, GMS Integration, Award Validation
      If (GMS_OIE_INT_PKG.IsGrantsEnabled() And p_award_id Is Not Null And Not GMS_OIE_INT_PKG.DoGrantsValidation(p_project_id            => p_project_id
                                                                                                                 ,p_task_id               => p_task_id
                                                                                                                 ,p_award_id              => p_award_id
                                                                                                                 ,p_award_number          => p_award_number
                                                                                                                 ,p_expenditure_type      => pa_rec.new_expenditure_type
                                                                                                                 ,p_expenditure_item_date => pa_rec.start_expense_date
                                                                                                                 ,p_calling_module        => 'SelfService'
                                                                                                                 ,p_err_msg               => p_return_error_message))
      Then
        AP_WEB_UTILITIES_PKG.AddExpError(l_errors, p_return_error_message, AP_WEB_UTILITIES_PKG.C_ErrorMessageType, 'GMS', 1, AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
      Else
        p_return_error_message := Null;
        lv_msg_type            := Null;
      End If;
    End If;
    If (p_return_error_message Is Null And pa_rec.end_expense_date Is Not Null)
    Then
      AP_WEB_PROJECT_PKG.ValidatePATransaction(p_project_id          => p_project_id
                                              ,p_task_id             => p_task_id
                                              ,p_ei_date             => pa_rec.end_expense_date
                                              ,p_expenditure_type    => pa_rec.new_expenditure_type
                                              ,p_non_labor_resource  => Null
                                              ,p_person_id           => pa_rec.employee_id
                                              ,p_quantity            => Null
                                              ,p_denom_currency_code => pa_rec.reimbursement_currency_code
                                              ,p_acct_currency_code  => pa_rec.base_currency_code
                                              ,p_denom_raw_cost      => p_amount
                                              ,p_acct_raw_cost       => ln_acct_raw_cost
                                              ,p_acct_rate_type      => pa_rec.default_exchange_rate_type
                                              ,p_acct_rate_date      => pa_rec.week_end_date
                                              ,p_acct_exchange_rate  => ln_default_exchange_rate
                                              ,p_transfer_ei         => Null
                                              ,p_incurred_by_org_id  => p_expenditure_org_id
                                              ,p_nl_resource_org_id  => Null
                                              ,p_transaction_source  => Null
                                              ,p_calling_module      => 'SelfService'
                                              ,p_vendor_id           => ln_vendor_id
                                              ,p_entered_by_user_id  => Null
                                              ,p_attribute_category  => pa_rec.line_attribute_category
                                              ,p_attribute1          => pa_rec.line_attribute1
                                              ,p_attribute2          => pa_rec.line_attribute2
                                              ,p_attribute3          => pa_rec.line_attribute3
                                              ,p_attribute4          => pa_rec.line_attribute4
                                              ,p_attribute5          => pa_rec.line_attribute5
                                              ,p_attribute6          => pa_rec.line_attribute6
                                              ,p_attribute7          => pa_rec.line_attribute7
                                              ,p_attribute8          => pa_rec.line_attribute8
                                              ,p_attribute9          => pa_rec.line_attribute9
                                              ,p_attribute10         => pa_rec.line_attribute10
                                              ,p_attribute11         => pa_rec.line_attribute11
                                              ,p_attribute12         => pa_rec.line_attribute12
                                              ,p_attribute13         => pa_rec.line_attribute13
                                              ,p_attribute14         => pa_rec.line_attribute14
                                              ,p_attribute15         => pa_rec.line_attribute15
                                              ,p_msg_type            => lv_msg_type
                                              ,p_msg_data            => p_return_error_message
                                              ,p_billable_flag       => lv_procedure_billable_flag);
    
      If (p_return_error_message Is Not Null And lv_msg_type = AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeError)
      Then
        AP_WEB_UTILITIES_PKG.AddExpError(l_errors, p_return_error_message, lv_msg_type, 'PATC', 1, AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
      Else
        -- Bug: 6936055, GMS Integration, Award Validation
        If (GMS_OIE_INT_PKG.IsGrantsEnabled() And p_award_id Is Not Null And Not GMS_OIE_INT_PKG.DoGrantsValidation(p_project_id            => p_project_id
                                                                                                                   ,p_task_id               => p_task_id
                                                                                                                   ,p_award_id              => p_award_id
                                                                                                                   ,p_award_number          => p_award_number
                                                                                                                   ,p_expenditure_type      => pa_rec.new_expenditure_type
                                                                                                                   ,p_expenditure_item_date => pa_rec.end_expense_date
                                                                                                                   ,p_calling_module        => 'SelfService'
                                                                                                                   ,p_err_msg               => p_return_error_message))
        Then
          AP_WEB_UTILITIES_PKG.AddExpError(l_errors, p_return_error_message, AP_WEB_UTILITIES_PKG.C_ErrorMessageType, 'GMS', 1, AP_WEB_UTILITIES_PKG.C_PATCMessageCategory, AP_WEB_UTILITIES_PKG.IsMobileApp);
        Else
          p_return_error_message := Null;
          lv_msg_type            := Null;
        End If;
      End If;
    End If;
  
    fnd_msg_pub.count_and_get(p_count => p_msg_count, p_data => p_msg_data);
  
  Exception
    When Others Then
      fnd_msg_pub.count_and_get(p_count => p_msg_count, p_data => p_msg_data);
  End ValidateProjectAccounting;

  -- Bug: 6631437, CC Segment not rendered if it has a parent segment
  Procedure GetDependentSegmentValue(p_employee_id In Number
                                    ,p_vset_name   In Varchar2
                                    ,p_seg_value   Out Nocopy Varchar2) Is
    l_EmpInfoRec           AP_WEB_DB_HR_INT_PKG.EmployeeInfoRec;
    l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%Type;
    l_dep_seg_num          Number;
    l_segments             FND_FLEX_EXT.SEGMENTARRAY;
    l_num_of_segments      Number;
  Begin
    -- Fetch the Employee Info
    If (AP_WEB_DB_HR_INT_PKG.GetEmployeeInfo(p_employee_id, l_EmpInfoRec))
    Then
      Null;
    End If;
    -- Fetch the Chart of Accounts
    If (AP_WEB_DB_AP_INT_PKG.GetCOAofSOB(l_chart_of_accounts_id))
    Then
      Null;
    End If;
    -- Get the dependent segment number
    If (GetDependentSegment(p_vset_name, l_chart_of_accounts_id, l_dep_seg_num))
    Then
      Null;
    End If;
    -- Get the segments
    If (FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#', l_chart_of_accounts_id, l_EmpInfoRec.emp_ccid, l_num_of_segments, l_segments))
    Then
      -- Get the segment value
      p_seg_value := l_segments(l_dep_seg_num);
    End If;
  
  Exception
    When Others Then
      AP_WEB_DB_UTIL_PKG.RaiseException('GetDependentSegmentValue');
      APP_EXCEPTION.RAISE_EXCEPTION;
    
  End GetDependentSegmentValue;

End AP_WEB_ACCTG_PKG;
/
